use master
go
ALTER DATABASE InventoryManagement
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE InventoryManagement
go
CREATE DATABASE InventoryManagement
go
use InventoryManagement
GO
CREATE TABLE Roles
(
	roleID varchar(5) not null primary key,
	roleName nvarchar(30) not null
)

GO

CREATE TABLE Staffs
(
	username nvarchar(32) not null primary key,
	passwd nvarchar(100) not null,
	fullname nvarchar(50),
	phone varchar(12),
	email nvarchar(100),
	[address] text,
	roleID varchar(5) not null foreign key references Roles
)

GO

CREATE TABLE Category
(
	categoryID varchar(10) not null primary key,
	categoryName nvarchar(30) not null
)

GO

CREATE TABLE Items
(
	itemID varchar(5) not null primary key,
	itemName nvarchar(25) not null,
	unit varchar(10) not null,
	status int not null,
	price float not null,
	returnable bit,
	categoryID varchar(10) not null foreign key references Category
)

go

CREATE TABLE Bills
(
	billID varchar(5) not null primary key,
	username nvarchar(32) not null foreign key references Staffs,
	date datetime not null,
	customerName nvarchar(30) not null,
	payment varchar(10) not null
)
go

CREATE TABLE BillDetails
(
	billID varchar(5) not null foreign key references Bills,
	itemID varchar(5) not null foreign key references Items,
	quantity int not null,
	CONSTRAINT PK_BillDetails PRIMARY KEY (billID, itemID)
)

go
CREATE TABLE RecieptDetails
(
	billID varchar(5) not null foreign key references Bills,
	itemID varchar(5) not null foreign key references Items,
	quantity int not null,
	returnDate date not null,
	CONSTRAINT PK_RecieptDetails PRIMARY KEY (billID, itemID)
)
go
CREATE PROC sp_addItem
 @id varchar(5), @name nvarchar(25), @unit varchar(10), @status int, @price float,
 @categoryID varchar(10), @returnable bit
 AS
 insert into Items values (@id, @name, @unit, @status, @price, @returnable,  @categoryID);

GO
 CREATE PROC sp_updateItem
 @id varchar(5), @name nvarchar(25), @unit varchar(10), @status int, @price float, @returnable bit,
 @categoryID varchar(10)
 AS
 update Items  
	set itemName = @name,
		unit = @unit, [status] = @status, price= @price, categoryID = @categoryID, returnable =  @returnable
		where itemID = @id
Go
CREATE PROC sp_removeItem
@id varchar(5)
AS
DELETE FROM Items WHERE itemId = @id

go
CREATE PROC sp_addCate
 @id varchar(10), @name nvarchar(30)
 AS
 insert into Category values (@id, @name);

GO
 CREATE PROC sp_updateCate
 @oldID varchar(5), @id varchar(10), @name nvarchar(30)
 AS
 update Category  
	set categoryID =@id, categoryName = @name
		where categoryID = @oldID
		
Go
CREATE PROC sp_removeCate
@id varchar(5)
AS
DELETE FROM Category WHERE categoryID = @id
DELETE FROM Items WHERE itemId = @id
GO

CREATE PROC sp_addAccount
@user nvarchar(20), @passwd varchar(100), @fullname nvarchar(50), @phone varchar(12), @email nvarchar(30),
 @address text, @roleID varchar(5)
 AS
 INSERT INTO Staffs values (@user, @passwd, @fullname, @phone, @email, @address, @roleID )
 
 GO
 
 CREATE PROC sp_updateAccount
 @oldUser nvarchar(20), @user nvarchar(20),@fullname nvarchar(50), @passwd varchar(100), @phone varchar(12),
  @email nvarchar(30),@address text, @roleID varchar(5)
 AS
 UPDATE Staffs
 set username =@user, passwd = @passwd, fullname = @fullname,
		phone = @phone, email = @email, [address]= @address, roleID = @roleID
		where username = @oldUser 
		
GO

CREATE PROC sp_updateAccountWithoutPassword
 @oldUser nvarchar(20), @user nvarchar(20), @fullname nvarchar(50), @phone varchar(12),
  @email nvarchar(30),@address text, @roleID varchar(5)
 AS
 UPDATE Staffs
 set username =@user, fullname = @fullname,
		phone = @phone, email = @email, [address]= @address, roleID = @roleID
		where username = @oldUser 
		
GO
CREATE PROC sp_removeAccount
@user nvarchar(20)
AS
DELETE FROM Staffs WHERE username = @user
GO
CREATE PROC sp_addBill
@billID varchar(5), @username nvarchar(32), @date datetime, @customerName nvarchar(30), @payment varchar(10)
 AS
 INSERT INTO Bills values ( @billID, @username, @date, @customerName, @payment )
 
 GO
 
 CREATE PROC sp_addBillDetail
@billID varchar(5), @itemID varchar(5), @quantity int
 AS
 INSERT INTO BillDetails values ( @billID, @itemID, @quantity )
 GO
 
 
 GO
 CREATE PROC sp_updateItemQuantity
 @oldID varchar(5),@status int
 AS
 update Items  
	set [status] = @status
		where itemID = @oldID
		GO
CREATE PROC sp_removeBill
@id varchar(5)
AS
DELETE FROM BillDetails WHERE billID = @id
DELETE FROM Bills WHERE billID = @id
GO
CREATE PROC sp_addRecieptDetail
@billID varchar(5), @itemID varchar(5), @quantity int, @date date
 AS
 INSERT INTO RecieptDetails values ( @billID, @itemID, @quantity, @date )
 GO
 USE InventoryManagement

GO
--INSERT INTO TABLE Roles
INSERT INTO Roles VALUES ('AD', 'Administrator');
INSERT INTO Roles VALUES ('SM', 'Sales Manager');
INSERT INTO Roles VALUES ('IM', 'Inventory Manager');
INSERT INTO Roles VALUES ('SP', 'Sales Person');
go
--INSERT INTO TABLE Staffs
INSERT INTO Staffs VALUES('admin', 'e10adc3949ba59abbe56e057f20f883e','Administrator', '123456789', 'admin@fpt.com.vn', 'Ha noi, viet Nam', 'AD');
INSERT INTO Staffs VALUES('salesmanager', 'e10adc3949ba59abbe56e057f20f883e','FirstName', '123456789', 'sales.manager@fpt.com.vn', 'Ha noi, viet Nam', 'SM');
INSERT INTO Staffs VALUES('inventorymanager', 'e10adc3949ba59abbe56e057f20f883e','FirstName', '123456789', 'inventory.manager@fpt.com.vn', 'Ha noi, viet Nam', 'IM');
INSERT INTO Staffs VALUES('salesperson', 'e10adc3949ba59abbe56e057f20f883e','FirstName', '123456789', 'sales.person@fpt.com.vn', 'Ha noi, viet Nam', 'SP');
go
--INSERT INTO TABLE CATEGORY
INSERT INTO Category VALUES ('FR','Fruit');
INSERT INTO Category VALUES ('VGT','Vegetable');
INSERT INTO Category VALUES ('FF','Fast food');
INSERT INTO Category VALUES ('FD','Food');
INSERT INTO Category VALUES ('DR','Drinks');
go
--INSERT INTO TABLE ITEMS
INSERT INTO Items VALUES ('APP', 'APPLE', 'Kilogam', 400, 3.0, 1,'FR');
INSERT INTO Items VALUES ('ORG', 'ORANGE', 'Kilogam', 500, 2.0, 1, 'FR');
INSERT INTO Items VALUES ('LMN', 'LEMON', 'Kilogam', 400, 1.5, 1, 'FR');
INSERT INTO Items VALUES ('STR', 'STRAWBERRY', 'Kilogam', 450, 5.0, 0, 'FR');
INSERT INTO Items VALUES ('GRA', 'GRAPES', 'Kilogam', 350, 7.0, 1, 'FR');
INSERT INTO Items VALUES ('TMT', 'TOMATO', 'Simple', 480, 2.0, 1, 'VGT');
INSERT INTO Items VALUES ('PTT', 'Potato', 'Siple', 340, 3.0, 1, 'VGT');
INSERT INTO Items VALUES ('CRR', 'Carrot', 'Kilogam', 420, 2.5, 1, 'VGT');
INSERT INTO Items VALUES ('KFC', 'KFC', 'Simple', 100, 10.0, 1, 'FF');
INSERT INTO Items VALUES ('SW', 'SANDWICH', 'Simple', 50, 4.0, 1, 'FF');
INSERT INTO Items VALUES ('PZ', 'PIZZA', 'Simple', 20, 10.0, 1, 'FF');
INSERT INTO Items VALUES ('MT', 'MEET', 'Kilogam', 300, 1.0, 1, 'FD');
INSERT INTO Items VALUES ('EG', 'EGGS', 'Simple', 100, 0.5, 1, 'FD');
INSERT INTO Items VALUES ('CCL', 'Cocacola', 'Simple', 70, 1.0, 1, 'DR');
INSERT INTO Items VALUES ('PS', 'Pepsi', 'Simple', 55, 1.0, 1, 'DR');
INSERT INTO Items VALUES ('7U', '7UP', 'Simple', 30, 1.0, 1, 'DR');

go
--INSERT INTO TABLE BILLS
INSERT INTO Bills VALUES ('BL1', 'salesperson', '1-1-2012', 'HuyPV', 'Direct');
INSERT INTO Bills VALUES ('BL2', 'salesperson', '1-2-2012', 'ThienNP', 'Direct');
INSERT INTO Bills VALUES ('BL3', 'salesperson', '1-3-2012', 'NgocNTN', 'Direct');
INSERT INTO Bills VALUES ('BL4', 'salesperson', '1-4-2012', 'VinhDL','Direct');

go
--INSERT INTO TABLE BILLDETAILS
INSERT INTO BillDetails VALUES ('BL1', 'APP', 2);
INSERT INTO BillDetails VALUES ('BL1', 'GRA', 1);
INSERT INTO BillDetails VALUES ('BL1', 'TMT', 1);
INSERT INTO BillDetails VALUES ('BL2', 'CCL', 5);
INSERT INTO BillDetails VALUES ('BL2', 'MT', 5);
INSERT INTO BillDetails VALUES ('BL2', 'ORG', 1);
INSERT INTO BillDetails VALUES ('BL3', 'SW', 2);
INSERT INTO BillDetails VALUES ('BL3', '7U', 5);
INSERT INTO BillDetails VALUES ('BL3', 'EG', 10);
INSERT INTO BillDetails VALUES ('BL4', 'PS', 5);
INSERT INTO BillDetails VALUES ('BL4', 'PTT', 1);
INSERT INTO BillDetails VALUES ('BL4', 'MT', 1);

